{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GUIでデータベースに接続する\n",
    "\n",
    "* データの取得部分をSQLから取得するように変更する"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1ファイルにGUI及びDB処理を記述"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "%%bash\n",
    "### 変数の設定\n",
    "name=GUISql\n",
    "classpath=\".:/home/prefuser/jupyter/python/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/root/git_jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "#classpath=\".:/Users/ftakao2007/jupyter/jupyter_notebook/Java/postgresql-42.1.1.jar\"\n",
    "\n",
    "\n",
    "### ソースの編集\n",
    "cat <<- EOS > ${name}.java\n",
    "\n",
    "import java.awt.*;\n",
    "import java.awt.event.*;\n",
    "import java.sql.Connection;\n",
    "import java.sql.DriverManager;\n",
    "import java.sql.ResultSet;\n",
    "import java.sql.ResultSetMetaData;\n",
    "import java.sql.Statement;\n",
    "//#import java.util.ArrayList;\n",
    "//#import java.util.List;\n",
    "\n",
    "class PrefFrame extends Frame implements ActionListener {\n",
    "    //# コンポーネントを定義。各クラスのメソッドが利用できるようにメソッド外で定義する\n",
    "    Label lb1;\n",
    "    TextArea txtar1;\n",
    "    Button btn1, btn2, btn3;\n",
    "    \n",
    "    public PrefFrame(String title) {\n",
    "        setTitle(title);\n",
    "\n",
    "        addWindowListener(new WindowAdapter() {\n",
    "            public void windowClosing(WindowEvent e) {\n",
    "                System.exit(0);\n",
    "            }\n",
    "        });\n",
    "\n",
    "        //# ラベル。BorderLayoutの頭にラベルを表示\n",
    "        lb1 = new Label(\"ボタンをクリックしてください。\",Label.CENTER);\n",
    "        add(lb1, BorderLayout.NORTH);\n",
    "        \n",
    "        //# テキストエリア。BorderLayoutの中央にテキストエリアを表示\n",
    "        txtar1 = new TextArea();\n",
    "        add(txtar1, BorderLayout.CENTER);\n",
    "        \n",
    "        //# パネル。GridLayoutを指定\n",
    "        Panel pn1 = new Panel();\n",
    "        pn1.setLayout(new GridLayout(1, 3));\n",
    "        \n",
    "        //# ボタンの設定とパネルへ追加\n",
    "        btn1 = new Button(\"表示\");\n",
    "        btn1.addActionListener(this);\n",
    "        pn1.add(btn1);\n",
    "        \n",
    "        //# ボタンの設定とパネルへ追加\n",
    "        btn2 = new Button(\"クリア\");\n",
    "        btn2.addActionListener(this);\n",
    "        pn1.add(btn2);\n",
    "\n",
    "        //# ボタンの設定とパネルへ追加\n",
    "        btn3 = new Button(\"終了\");\n",
    "        btn3.addActionListener(this);\n",
    "        pn1.add(btn3);\n",
    "        \n",
    "        //# パネル。BorderLayoutの下にパネルを表示\n",
    "        add(pn1, BorderLayout.SOUTH);\n",
    "    }\n",
    "  \n",
    "    //# イベントが発生すると呼び出されるイベントハンドラのactionPerfomed()メソッド\n",
    "    public void actionPerformed (ActionEvent e) {\n",
    "        if (e.getSource() == btn1) {\n",
    "            prefDisplay();\n",
    "        } else if (e.getSource() == btn2) {\n",
    "            txtar1.setText(\"\");\n",
    "        } else {\n",
    "            System.exit(0);\n",
    "        }\n",
    "    }\n",
    "    \n",
    "    //#private void prefDisplay () {\n",
    "    private void prefDisplay () {\n",
    "        Statement statement = null;\n",
    "        ResultSet resultSet = null;\n",
    "        Connection connection = null;\n",
    "        \n",
    "        try {\n",
    "            //# DB接続\n",
    "            Class.forName(\"org.postgresql.Driver\");\n",
    "            connection = DriverManager.getConnection(\"jdbc:postgresql://10.136.3.189:5432/jsd_development\", \"jsd\", \"pass\");\n",
    "            statement = connection.createStatement();\n",
    "            \n",
    "            //# SQL発行\n",
    "            resultSet = statement.executeQuery(\"SELECT * FROM people\");\n",
    "\n",
    "            //# 値の取得\n",
    "            StringBuffer temp = new StringBuffer();\n",
    "\n",
    "            while(resultSet.next()){\n",
    "                int id = resultSet.getInt(\"id\");\n",
    "                String name = resultSet.getString(\"name\");\n",
    "                int age = resultSet.getInt(\"age\");\n",
    "                String address = resultSet.getString(\"address\");\n",
    "                temp.append(id + \" \\n\" + \"    name: \" + name + \"\\n    age: \" + age + \"\\n    address: \" + address + \"\\n\\n\");\n",
    "            }\n",
    "\n",
    "            txtar1.setText(temp.toString());\n",
    "\n",
    "            //# DB切断\n",
    "            statement.close();\n",
    "            connection.close();\n",
    "            connection.close();\n",
    "            \n",
    "        } catch(Exception e) {\n",
    "            System.out.println(\"例外発生：\" + e );\n",
    "        }\n",
    "    }\n",
    "}\n",
    "\n",
    "public class $name {\n",
    "    public static void main(String args[]){\n",
    "    \n",
    "        PrefFrame frm = new PrefFrame(\"都道府県マスター\");\n",
    "        //# フレームの設定\n",
    "        frm.setLocation(300,200);\n",
    "        frm.setSize(250,350);\n",
    "        frm.setBackground(Color.LIGHT_GRAY);\n",
    "        frm.setVisible(true);\n",
    "    }\n",
    "}\n",
    "EOS\n",
    "\n",
    "### コンパイル\n",
    "#javac ${name}.java\n",
    "javac -encoding UTF-8 ${name}.java\n",
    "\n",
    "### 実行\n",
    "java -classpath ${classpath} ${name}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
